package com.uniview.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.springframework.orm.hibernate4.SessionFactoryUtils;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.uniview.base.BaseDao;
import com.uniview.entity.po.Employees;
import com.uniview.entity.vo.Pager;
import com.uniview.entity.vo.QueryBuilder;

@Repository("employeesDao")
public class EmployeesDao extends BaseDao<Employees> {

	@Resource
	protected SessionFactory sessionFactory;

	private Logger logger = Logger.getLogger(EmployeesDao.class);
	
	

	/**
	 * 批量保存人员信息
	 * 
	 * @param list
	 * @return
	 * @throws Exception
	 */
	public boolean saveEmployees(List<Employees> list, int batchNum) throws Exception {
		return this.save(list, batchNum);
	}

	/**
	 * JDBC 批量保存人员信息
	 * 
	 * @param list
	 * @param batchNum
	 * @throws Exception
	 */
	@Transactional(propagation = Propagation.REQUIRED)
	public void saveEmployeesBySql(List<Employees> list, int batchNum) throws Exception {
		String sql = "insert into sd_employees (EmpCode, OrgName, EmpName, Position, LayerFlag, SortBy, Phone, Email, CreatedDate, Remark) "
				+ "values (?, ?, ?, ?, ?, ?, ?, ?, now(), ?)";
		Connection conn = SessionFactoryUtils.getDataSource(this.sessionFactory).getConnection();

		PreparedStatement ps = null;
		ps = conn.prepareStatement(sql);
		Employees emp = null;
		for (int i = 0; i < list.size(); i++) {
			emp = list.get(i);
			ps.setString(1, emp.getEmpCode());
			ps.setString(2, emp.getOrgName());
			ps.setString(3, emp.getEmpName());
			ps.setString(4, emp.getPosition());
			ps.setString(5, emp.getLayerFlag());
			ps.setString(6, emp.getSortBy());
			ps.setString(7, emp.getPhone());
			ps.setString(8, emp.getEmail());
			ps.setString(9, emp.getRemark());
			ps.addBatch();
			if (i % batchNum == 0) {
				ps.executeBatch();
				conn.commit();
			}
		}
		ps.executeBatch();
		conn.commit();
		ps.close();
	}

	/**
	 * 保存人员信息
	 * 
	 * @param employees
	 * @return
	 * @throws Exception
	 */
	public int saveEmployees(Employees employees) throws Exception {
		return (int) this.save(employees);
	}

	/**
	 * 更新人员信息
	 * 
	 * @param employees
	 * @return
	 * @throws Exception
	 */
	public boolean updateEmployees(Employees employees) throws Exception {
		return this.update(employees);
	}

	/**
	 * 少批量更新人员信息
	 * 
	 * @param list
	 * @return
	 * @throws Exception
	 */
	public boolean updateEmployees(List<Employees> list) throws Exception {
		for (Employees e : list) {
			this.update(e);
		}
		return true;
	}

	/**
	 * 更新人员离职
	 * 
	 * @param eId
	 * @param updateDate
	 * @return
	 * @throws Exception
	 */
	public boolean updateLeaveEmployees(int eId, Date updateDate) throws Exception {
		String hql = "update Employees e set e.LastUpdatedDate = ?, e.Remark='3' where e.eId = ?";
		return this.updateByHql(hql, updateDate, eId);
	}

	/**
	 * 查询全部人员信息
	 */
	public List<Employees> findAll() throws Exception {
		String sql = "select * from sd_employees order by empcode desc";
		return this.findBySql(sql);
	}

	/**
	 * 根据ID查询人员信息
	 * 
	 * @param eId
	 * @return
	 * @throws Exception
	 */
	public Employees findEmployeesById(int eId) throws Exception {
		return this.find(eId);
	}

	/**
	 * 根据工号查找用户信息排除离职人员
	 * 
	 * @param workNo
	 * @return
	 * @throws Exception
	 */
	public Employees findEmployeesByWorkNo(String workNo) throws Exception {
		String sql = "select * from sd_employees where UPPER(empCode) = '" + workNo.toUpperCase() + "' and Remark <> 3";
		List<Employees> list = this.findBySql(sql);
		if (list != null && list.size() > 0) {
			return list.get(0);
		}

		return null;
	}

	/**
	 * 根据工号查找用户信息,不管是否离职
	 * 
	 * @param workNo
	 * @return
	 * @throws Exception
	 */
	public Employees findEmployeesByEmpCodeWithoutRemark(String empCode) throws Exception {
		String sql = "select * from sd_employees where empCode = '" + empCode + "'";
		List<Employees> list = this.findBySql(sql);
		if (list != null && list.size() > 0) {
			return list.get(0);
		}
		return null;
	}

	/**
	 * 分页查询人员信息
	 * 
	 * @param queryBuilder
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @throws Exception
	 */
	public Pager<Employees> findPageEmployees(int pageNo, int pageSize) throws Exception {
		return this.findPagerByQuery(pageNo, pageSize, "from Employees where Remark <> 3 order by eId");
		// return this.findPagerByQueryBuilder(queryBuilder, pageNo, pageSize);
	}

	public Pager<Employees> findPageEmployees(QueryBuilder queryBuilder, int pageNo, int pageSize) throws Exception {
		return this.findPagerByQueryBuilder(queryBuilder, pageNo, pageSize);
	}

	/**
	 * 根据ID删除人员信息
	 * 
	 * @param eId
	 * @return
	 * @throws Exception
	 */
	public boolean deleteEmployeesById(int eId) throws Exception {
		return this.deleteById(eId);
	}

	public boolean deleteAll() throws Exception {
		String hql = "delete from Employees";
		return this.deleteByHql(hql);
	}

	/**
	 * 获取总记录数
	 * 
	 * @return
	 * @throws Exception
	 */
	public Long getTotalRows() throws Exception {
		return this.findTotalRow();
	}

	/**
	 * 根据更新日期 统计记录数
	 * 
	 * @param beginDate
	 * @param endDate
	 * @return
	 * @throws Exception
	 */
	public Integer getTotalRowsByUpdateTime(String beginDate, String endDate) throws Exception {
		// String hql = "select count(*) from Employees e where
		// (e.LastUpdatedDate >= ? and e.LastUpdatedDate <= ?) or (e.CreateDate
		// >= ? and e.CreatedDate <= ?)";
		String countSql = "select count(*) from sd_employees e where (e.LastUpdatedDate between '" + beginDate
				+ "' and '" + endDate + "') or (e.CreatedDate between '" + beginDate + "' and '" + endDate + "')";
		// return this.findTotalRow(hql, beginDate, endDate, beginDate,
		// endDate);
		return this.findTotalRowBySql(countSql);
	}

	/**
	 * 根据更新时间查询人员信息
	 * 
	 * @param beginDate
	 * @param endDate
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @throws Exception
	 */
	public List<Employees> findEmployeesByUpdateTime(String beginDate, String endDate) throws Exception {
		// String sql = "select * from sd_employees e where (e.LastUpdatedDate
		// >= ? and e.LastUpdatedDate <= endDate) or (e.CreatedDate >= ? and
		// e.CreatedDate <= ?) "
		// + "order by eId";
		String sql = "select * from sd_employees e where (e.LastUpdatedDate between '" + beginDate + "' and '" + endDate
				+ "') or (e.CreatedDate between '" + beginDate + "' and '" + endDate + "')";
		// return this.findBySql(sql, beginDate, endDate, beginDate, endDate);
		return this.findBySql(sql);
	}

	/**
	 * 根据账号（工号）查询雇员
	 * 
	 * @param userName
	 * @return
	 * @throws Exception
	 */
	public List<Employees> findEmployeesByName(String userName) throws Exception {
		String sql = "select * from sd_employees e where UPPER(e.EmpCode) = ?";
		return this.findBySql(sql, userName);
	}

	/**
	 * 根据员工ID获取员工名称
	 * 
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public String findEmployeesNameById(int id) throws Exception {
		Employees emp = this.find(id);
		if (null != emp) {
			return emp.getEmpName();
		}
		return "";
	}

	public String findEmployeesNameByCode(String code) throws Exception {
		List<Employees> list = this.findBySql("select * from sd_employees where EmpCode=?", code);
		if (null != list && list.size() == 1) {
			return list.get(0).getEmpName();
		}
		return "";
	}

	/**
	 * 根据员工名称进行模糊查询,获取员工id
	 * 
	 * @param name
	 * @return
	 * @throws Exception
	 */
	public String findEmployeesIdByName(String name) throws Exception {
		String sql = "select * from sd_employees e where e.EmpName like '%" + name + "%' and e.Remark <> 3";
		List<Employees> list = this.findBySql(sql);
		String codes = "";
		if (null != list && !list.isEmpty()) {
			for (Employees emp : list) {
				codes += "'" + emp.getEmpCode() + "',";
			}
			codes = codes.substring(0, codes.length() - 1);
		}
		return codes;
	}

	/**
	 * 根据员工部门进行模糊查询获取员工工号
	 * 
	 * @param dept
	 * @return
	 * @throws Exception
	 */
	public String findEmployeesCodeByLikeDept(String dept) throws Exception {
		String sql = "select * from sd_employees e where e.OrgName like '%" + dept + "%' and e.Remark <> 3";
		List<Employees> list = this.findBySql(sql);
		String codes = "";
		if (null != list && !list.isEmpty()) {
			for (Employees emp : list) {
				codes += "'" + emp.getEmpCode() + "',";
			}
			codes = codes.substring(0, codes.length() - 1);
		}
		return codes;
	}

	public String findEmeployeesCodeByLikeEmpNameAndOrgName(String empName, String orgName) throws Exception {
		String sql = "select * from sd_employees e where e.EmpName like '%" + empName + "%' and  e.OrgName like '%"
				+ orgName + "%' and e.Remark <> 3";
		List<Employees> list = this.findBySql(sql);
		String codes = "";
		if (null != list && !list.isEmpty()) {
			for (Employees emp : list) {
				codes += "'" + emp.getEmpCode() + "',";
			}
			codes = codes.substring(0, codes.length() - 1);
		}
		return codes;
	}

	/**
	 * 查詢日報的可視範圍
	 * 
	 * @param jid
	 * @return
	 * @throws Exception
	 */
	public List<Employees> findEmployessByJournalId(int jid) throws Exception {
		String sql = "SELECT e.* FROM un_visiblerange v ,sd_employees e WHERE v.userid = e.empcode AND v.jid = " + jid;
		List<Employees> list = this.findBySql(sql);
		return list;

	}

	/**
	 * 查詢国际日报的可視範圍
	 * 
	 * @param jid
	 * @return
	 * @throws Exception
	 */
	public List<Employees> findEmployessByINTJournalId(int jid) throws Exception {
		String sql = "SELECT e.* FROM un_int_visiblerange v ,sd_employees e WHERE v.userid = e.empcode AND v.jid = "
				+ jid;
		List<Employees> list = this.findBySql(sql);
		return list;

	}

	/**
	 * 查詢工时管理的可視範圍
	 * 
	 * @param jid
	 * @return
	 * @throws Exception
	 */
	public List<Employees> findEmployessByManhoursId(int jid) throws Exception {
		String sql = "SELECT e.* FROM un_mh_visiblerange v ,sd_employees e WHERE v.userid = e.empcode AND v.jid = "
				+ jid;
		List<Employees> list = this.findBySql(sql);
		return list;

	}

	/**
	 * 根据工号查询雇员是否已存在
	 * 
	 * @param empCode
	 * @return
	 * @throws Exception
	 */
	public boolean checkEmployeeByEmpCode(String empCode) throws Exception {
		String sql = "select count(*) from sd_employees where EmpCode = '" + empCode + "'";
		int total = this.findTotalRowBySql(sql);
		return total > 0;
	}

	/**
	 * 统计指定部门及其下属部门的员工数量（不包含指定工号的人）
	 * 
	 * @param orgName
	 * @param workNo
	 * @return
	 * @throws Exception
	 */
	public int countEmployeesByOrgNameWithoutCharge(String orgName, String workNo) throws Exception {
		String parent = "";
		if (!orgName.endsWith("/")) {
			parent = orgName + "/";
		} else {
			parent = orgName;
			orgName = orgName.substring(0, orgName.length() - 1);
		}
		String sql = "select count(*) from sd_employees where (OrgName = '" + orgName + "' or OrgName like '%" + parent
				+ "%') and EmpCode <> '" + workNo + "' and Remark <> 3";

		return this.findTotalRowBySql(sql);

	}

	public int countEmployeesByOrgNameWithoutCharge(String orgName, String workNo, String dept) throws Exception {
		String parent = "";
		if (!orgName.endsWith("/")) {
			parent = orgName + "/";
		} else {
			parent = orgName;
			orgName = orgName.substring(0, orgName.length() - 1);
		}
		String sql = "select count(*) from sd_employees where (OrgName = '" + orgName + "' or OrgName like '%" + parent
				+ "%') and EmpCode <> '" + workNo + "' and Remark <> 3";
		if (StringUtils.isNotBlank(dept)) {
			sql += " and (OrgName like '%" + dept + "%' or EmpName like '%" + dept + "%')";
		}
		return this.findTotalRowBySql(sql);

	}

	/**
	 * 分页查询指定部门的下属员工
	 * 
	 * @param orgName
	 * @param workNo
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @throws Exception
	 */
	public List<Employees> findPagerEmployeesByOrgNameWithoutCharge(String orgName, String workNo, String dept,
			int pageNo, int pageSize) throws Exception {
		String parent = "";
		if (!orgName.endsWith("/")) {
			parent = orgName + "/";
		} else {
			parent = orgName;
			orgName = orgName.substring(0, orgName.length() - 1);
		}
		String sql = "select * from sd_employees where (OrgName = '" + orgName + "' or OrgName like '%" + parent
				+ "%') and EmpCode <> '" + workNo + "' and Remark <> 3";

		if (StringUtils.isNotBlank(dept)) {
			sql += " and (OrgName like '%" + dept + "%' or EmpName like '%" + dept + "%')";
		}
		sql += " order by EmpCode";

		return this.findPagerBySql(sql, pageNo, pageSize);
	}

	public Pager<Employees> findAllEmployeesByPage(String empCode, String dept, int pageNo, int pageSize)
			throws Exception {

		String sql = "from Employees where 1=1 ";
		if (StringUtils.isNotBlank(empCode)) {
			sql += " and EmpCode like upper('%" + empCode.toUpperCase() + "%') or EmpName like '%"+empCode+"%' ";
		}

		if (StringUtils.isNotBlank(dept)) {
			sql += " and OrgName like '%" + dept + "%' ";
		}
		sql += " and remark <> 3 order by EmpCode ";

		return this.findPagerByQuery(pageNo, pageSize, sql);
	}

	/**
	 * 查询指定部门的下属员工
	 * 
	 * @param orgName
	 * @param workNo
	 * @return
	 * @throws Exception
	 */
	public List<Employees> findPagerEmployeesByOrgNameWithoutCharge(String orgName, String workNo, String dept)
			throws Exception {
		String parent = "";
		if (!orgName.endsWith("/")) {
			parent = orgName + "/";
		} else {
			parent = orgName;
			orgName = orgName.substring(0, orgName.length() - 1);
		}
		String sql = "select * from sd_employees where (OrgName = '" + orgName + "' or OrgName like '%" + parent
				+ "%') and EmpCode <> '" + workNo + "' and Remark <> 3";

		if (StringUtils.isNotBlank(dept)) {
			sql += " and (OrgName like '%" + dept + "%' or EmpName like '%" + dept + "%')";
		}
		sql += " order by EmpCode";

		return this.findBySql(sql);
	}

	public Employees findEmployeesByEmpCodeWithLike(String empCode) throws Exception {
		String sql = "select * from sd_employees where EmpCode like '%" + empCode + "%'";
		List<Employees> list = this.findBySql(sql);
		if (list != null && list.size() > 0) {
			return list.get(0);
		}
		return null;
	}

	public List<Employees> findJournalSummary(String orgName, String begin, String end, String workNo)
			throws Exception {
		String sql = "SELECT e.empName,e.empCode,e.orgName,count(j.owner) as count FROM sd_employees e left JOIN un_journal j"
				+ " ON e.empCode = j.workNo AND (j.createDate BETWEEN '" + begin + "' AND '" + end + "')"
				+ " WHERE e.orgName like '%" + orgName + "%'" + workNo
				+ " AND e.remark != 3 GROUP BY e.EmpCode ORDER BY j.createDate";
		logger.debug("========" + sql);
		// List<Employees> list = this.findBySql(sql);
		Query query = sessionFactory.getCurrentSession().createSQLQuery(sql);
		List<Object[]> objs = query.list();
		List<Employees> list = new ArrayList<>();
		for (int i = 0; i < objs.size(); i++) {
			Employees emp = new Employees();
			emp.setEmpName(String.valueOf(objs.get(i)[0]));
			emp.setEmpCode(String.valueOf(objs.get(i)[1]));
			emp.setOrgName(String.valueOf(objs.get(i)[2]));
			emp.setCount(Integer.parseInt(String.valueOf(objs.get(i)[3])));
			list.add(emp);
		}
		if (!list.isEmpty() && list.size() > 0) {
			return list;
		}
		return null;
	}

	public List<Employees> findJournalLabels(String name, String begin, String end, String workNo) throws Exception {
		String sql = "SELECT e.userName,e.userId,e.deptName,count(j.owner) as count FROM un_statistic_labels_users e left JOIN un_journal j"
				+ " ON e.userId = j.workNo AND (j.createDate BETWEEN '" + begin + "' AND '" + end + "')"
				+ " WHERE e.deptName like '%" + name + "%'" + workNo + " GROUP BY e.userId ORDER BY j.createDate";
		logger.debug("========" + sql);
		Query query = sessionFactory.getCurrentSession().createSQLQuery(sql);
		List<Object[]> objs = query.list();
		List<Employees> list = new ArrayList<>();
		for (int i = 0; i < objs.size(); i++) {
			Employees emp = new Employees();
			emp.setEmpName(String.valueOf(objs.get(i)[0]));
			emp.setEmpCode(String.valueOf(objs.get(i)[1]));
			emp.setOrgName(String.valueOf(objs.get(i)[2]));
			emp.setCount(Integer.parseInt(String.valueOf(objs.get(i)[3])));
			list.add(emp);
		}
		if (!list.isEmpty() && list.size() > 0) {
			return list;
		}
		return null;

	}

	/**
	 * 根据角色id,账号（姓名，工号，手机号）查询雇员
	 * 
	 * @param empNameOrCode
	 * @param phone
	 * @param bindType
	 * @param roleId
	 * @return
	 * @throws Exception
	 */
	public List<Employees> findEmployeesByCondition(String empNameOrCode, String phone, String bindType, Integer roleId)
			throws Exception {
		StringBuilder sb = new StringBuilder("select * from sd_employees se where 1=1");
		if (!StringUtils.isBlank(empNameOrCode)) {
			sb.append(" and (se.EMPNAME like '%" + empNameOrCode + "%' or se.EMPCODE like '%" + empNameOrCode + "%')");
		}
		if (!StringUtils.isBlank(phone)) {
			sb.append(" and se.PHONE like '" + phone + "%'");
		}
		if (bindType != null)
			if ("binded".equals(bindType)) {
				sb.append(" and exists (select sur.urid from sd_user_role sur where sur.userid=se.empcode and sur.roleid="
						+ roleId + ")");
			} else if ("unbind".equals(bindType)) {
				sb.append(
						" and not exists (select sur.urid from sd_user_role sur where sur.userid=se.empcode and sur.roleid="
								+ roleId + ")");
			}
		sb.append(" order by se.EMPCODE desc");
		return this.findBySql(sb.toString());
	}

	/**
	 * 根据角色id查询已绑定/未绑定用户列表
	 * 
	 * @param bindType
	 * @param roleId
	 * @return
	 * @throws Exception
	 */
	public List<Employees> findBindedOrUnbindEmployeesList(String bindType, Integer roleId,String empName) throws Exception {
		String sql = "";
		String query="";
		if(empName!=null&& !"".equals(empName) ){
			query="and empName like '%"+empName+"%' or UPPER(empCode) like '%"+empName.toUpperCase()+"%'";
		}
		if ("binded".equals(bindType)) {// 已绑定
			// sql = "select * from sd_employees se where exists (select sur.urid from
			// sd_user_role sur where sur.userid=se.eid and sur.roleid=?) order by
			// se.EMPCODE desc";
			sql = "select se.* from sd_employees se join sd_user_role sur on UPPER(sur.userid)=UPPER(se.EMPCODE) and sur.roleid=? order by sur.createdate desc";
		} else if ("unbind".equals(bindType)) {// 未绑定
			sql = "select * from sd_employees se where not exists (select sur.urid from sd_user_role sur where UPPER(sur.userid)=UPPER(se.EMPCODE) and sur.roleid=?) "+query+" order by se.EMPCODE desc";
		} else {
			return new ArrayList<Employees>();
		}
		return this.findBySql(sql, roleId);
	}

	/**
	 * 根据关键字查询用户
	 * 
	 * @param searchStr
	 * @param page
	 * @param limit
	 * @return
	 * @throws Exception
	 */
	public Pager<Employees> findEmployeesBySearch(String searchStr, String mangerNo, Integer page, Integer limit) throws Exception {
		/*String sql = "select e.* from sd_employees e " + 
				" right join sd_regionmanager rm " + 
				" on e.orgname like '%' || rm.regionname || '%' " + 
				" where upper(rm.workno) = upper('" + mangerNo + "') ";
		String countSql = "select count(1) from sd_employees e " + 
				" right join sd_regionmanager rm " + 
				" on e.orgname like '%' || rm.regionname || '%' " + 
				" where upper(rm.workno) = upper('" + mangerNo + "') ";*/
		
		//调整过滤条件，现在查找全部
		String sql = "select e.* from sd_employees e where 1=1 " ;
		String countSql = "select count(1) from sd_employees e where 1=1 ";
		if (StringUtils.isNotBlank(searchStr)) {
			sql += " and (e.empname like '%" + searchStr + "%' or e.empcode like upper('%" + searchStr + "%') or e.phone like '%" + searchStr + "%' or e.pinyin like '%" + searchStr + "%')";
			countSql += " and (e.empname like '%" + searchStr + "%' or e.empcode like upper('%" + searchStr + "%') or e.phone like '%" + searchStr + "%' or e.pinyin like '%" + searchStr + "%')";
		}
		sql += " and e.remark <> 3 order by e.empcode";
		
		Integer total = this.findTotalRowBySql(countSql);
		List<Employees> list = this.findPagerBySql(sql, page, limit);
		Pager<Employees> pager = new Pager<Employees>();
		pager.setPagerList(list);
		pager.setTotalRow(total);
		return pager;
	}
}
